package db.views;

import java.sql.SQLException;

import org.hsqldb.Types;

import db.DBMain;
import db.DBObject;
import db.tables.DBCategoryTable;
import db.tables.DBLanguageTable;
import db.tables.polyglott.DBCategoryPGTable;

public class DBTranslationView extends DBObject {
	public static final String TABLE_NAME="translation_view";
	
	public static final String CATEGORY_ID_COL = DBCategoryTable.CATEGORY_ID_COL,
			CATEGORY_NAME_COL = DBCategoryTable.CATEGORY_NAME_COL,
			LANGUAGE_SHORT_COL=DBLanguageTable.LANGUAGE_SHORT_COL,
			TRANSLATED_NAME_COL=DBCategoryPGTable.CATEGORY_NAME_PG_COL;
	
	protected static final String statement ="SELECT "+CATEGORY_ID_COL+","+CATEGORY_NAME_COL+","+LANGUAGE_SHORT_COL+","
		+TRANSLATED_NAME_COL+" FROM "+DBCategoryTable.TABLE_NAME+" JOIN "+ DBCategoryPGTable.TABLE_NAME+" ON ("
		+getAbsoluteColumn(DBCategoryTable.TABLE_NAME, DBCategoryTable.CATEGORY_ID_COL)+" = "
		+getAbsoluteColumn(DBCategoryPGTable.TABLE_NAME, DBCategoryPGTable.CATEGORY_ID_COL)+")" 
		+"JOIN "+DBLanguageTable.TABLE_NAME+" ON("
		+getAbsoluteColumn(DBCategoryPGTable.TABLE_NAME, DBCategoryPGTable.LANGUAGE_ID_COL)+" = "
		+getAbsoluteColumn(DBLanguageTable.TABLE_NAME, DBLanguageTable.LANGUAGE_ID_COL)+")"+";";

	public DBTranslationView() throws ClassNotFoundException, SQLException {
		super(TABLE_NAME, new Object[][] {
				{ CATEGORY_ID_COL, Types.BIGINT, "", "" },
				{ CATEGORY_NAME_COL, Types.VARCHAR, "", "" },
				{ LANGUAGE_SHORT_COL, Types.VARCHAR, "", "" },
				{ TRANSLATED_NAME_COL, Types.VARCHAR,"","" }},statement);
	}
	
	public String getTranslation(String word, String language){
		String result=null;
		Object[] o=selectObject(TRANSLATED_NAME_COL, 
				new String[]{CATEGORY_NAME_COL,LANGUAGE_SHORT_COL}, 
				new String[]{word,language});
		if(o.length>=1)
			result=(String)o[0];
		return result;
	}
	
	public String getTranslation(Long word, Long language){
		String result=null;
		Object[] o=DBMain.getCategoryPGTable().selectObject(DBCategoryPGTable.CATEGORY_NAME_PG_COL, 
				new String[]{DBCategoryPGTable.CATEGORY_ID_COL,DBCategoryPGTable.LANGUAGE_ID_COL}, 
				new Long[]{word,language});
		if(o.length>=1)
			result=(String)o[0];
		return result;
	}
	
	public int insertTranslation(Long word, Long language, String translation){
		return DBMain.getCategoryPGTable().insert(
				new String[]{DBCategoryPGTable.CATEGORY_ID_COL,DBCategoryPGTable.LANGUAGE_ID_COL,DBCategoryPGTable.CATEGORY_NAME_PG_COL}, 
				new Object[]{word,language,translation});
	}
}
